//----------------------------------------------------------------
//Tên Class: Cls_KhachHang
//Người thực hiện: NhatTHM
//Website: www.nhatthanh.net
//-----------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
namespace Library.Business {
	public partial class Cls_KhachHang
	{
		protected string sSQL;
		//Tham số cho Procudure
		#region Tham số cho Procudure
		protected const string st_KhachHang_Insert = "st_KhachHang_Insert";
		protected const string st_KhachHang_Update = "st_KhachHang_Update";
		protected const string st_KhachHang_Update_ALL = "st_KhachHang_Update_ALL";
		protected const string st_KhachHang_Update_Hidden = "st_KhachHang_Update_Hidden";
		protected const string st_KhachHang_Delete = "st_KhachHang_Delete";
		protected const string st_KhachHang_SelectAll = "st_KhachHang_SelectAll";
		protected const string st_KhachHang_SelectAll_Key = "st_KhachHang_SelectAll_Key";
		protected const string st_KhachHang_SelectByID = "st_KhachHang_SelectByID";
		protected const string st_KhachHang_SelectByID_Stand = "st_KhachHang_SelectByID_Stand";
		protected const string TABLE_NAME = "tbKhachHang";
		#endregion Tham số cho Procudure
		//Các tên filed của table.
		#region Các tên filed của table.
		public const string fn_ID_KhachHang = "ID_KhachHang";
		public const int len_ID_KhachHang = 4;
		public const string fn_MSKH = "MSKH";
		public const int len_MSKH = 30;
		public const string fn_ID_HeThongNuoc = "ID_HeThongNuoc";
		public const int len_ID_HeThongNuoc = 30;
		public const string fn_ID_LoaiHinhDoanhNghiep = "ID_LoaiHinhDoanhNghiep";
		public const int len_ID_LoaiHinhDoanhNghiep = 30;
		public const string fn_TenKhachHang = "TenKhachHang";
		public const int len_TenKhachHang = 100;
		public const string fn_TenKhachHangTat = "TenKhachHangTat";
		public const int len_TenKhachHangTat = 50;
		public const string fn_DiaChi = "DiaChi";
		public const int len_DiaChi = 80;
		public const string fn_MaSoThue = "MaSoThue";
		public const int len_MaSoThue = 30;
		public const string fn_DienThoai = "DienThoai";
		public const int len_DienThoai = 50;
		public const string fn_MucChiTeu = "MucChiTeu";
		public const int len_MucChiTeu = 4;
		public const string fn_Hidden = "Hidden";
		public const int len_Hidden = 1;
		//Các filed của table.
		private int _iD_KhachHang_find;
		private int _iD_KhachHang;
		private string _mSKH;
		private string _iD_HeThongNuoc;
		private string _iD_LoaiHinhDoanhNghiep;
		private string _tenKhachHang;
		private string _tenKhachHangTat;
		private string _diaChi;
		private string _maSoThue;
		private string _dienThoai;
		private int _mucChiTeu;
		private bool _hidden;
		#endregion Các tên filed của table.
		#region Các phương thức cho table.
		//Thuộc tính ID_KhachHang_find
		public int ID_KhachHang_find
		{
			get{return this._iD_KhachHang_find;}
			set{this._iD_KhachHang_find = value;}
		}
		//Thuộc tính ID_KhachHang
		public int ID_KhachHang
		{
			get{return this._iD_KhachHang;}
			set{this._iD_KhachHang = value;}
		}
		//Thuộc tính MSKH
		public string MSKH
		{
			get{return this._mSKH;}
			set{this._mSKH = value;}
		}
		//Thuộc tính ID_HeThongNuoc
		public string ID_HeThongNuoc
		{
			get{return this._iD_HeThongNuoc;}
			set{this._iD_HeThongNuoc = value;}
		}
		//Thuộc tính ID_LoaiHinhDoanhNghiep
		public string ID_LoaiHinhDoanhNghiep
		{
			get{return this._iD_LoaiHinhDoanhNghiep;}
			set{this._iD_LoaiHinhDoanhNghiep = value;}
		}
		//Thuộc tính TenKhachHang
		public string TenKhachHang
		{
			get{return this._tenKhachHang;}
			set{this._tenKhachHang = value;}
		}
		//Thuộc tính TenKhachHangTat
		public string TenKhachHangTat
		{
			get{return this._tenKhachHangTat;}
			set{this._tenKhachHangTat = value;}
		}
		//Thuộc tính DiaChi
		public string DiaChi
		{
			get{return this._diaChi;}
			set{this._diaChi = value;}
		}
		//Thuộc tính MaSoThue
		public string MaSoThue
		{
			get{return this._maSoThue;}
			set{this._maSoThue = value;}
		}
		//Thuộc tính DienThoai
		public string DienThoai
		{
			get{return this._dienThoai;}
			set{this._dienThoai = value;}
		}
		//Thuộc tính MucChiTeu
		public int MucChiTeu
		{
			get{return this._mucChiTeu;}
			set{this._mucChiTeu = value;}
		}
		//Thuộc tính Hidden
		public bool Hidden
		{
			get{return this._hidden;}
			set{this._hidden = value;}
		}
		#endregion Các phương thức cho table.
		/// <summary>
		/// Hàm khởi tạo không có tham số.
		/// </summary>
		public Cls_KhachHang()
		{
		}

		/// <summary>
		/// Hàm khởi tạo có  tham số.
		/// </summary>
		public Cls_KhachHang(string mskh, string id_hethongnuoc, string id_loaihinhdoanhnghiep, string tenkhachhang, string tenkhachhangtat, string diachi, string masothue, string dienthoai, int mucchiteu, bool hidden)
		{
			this._mSKH = mskh;
			this._iD_HeThongNuoc = id_hethongnuoc;
			this._iD_LoaiHinhDoanhNghiep = id_loaihinhdoanhnghiep;
			this._tenKhachHang = tenkhachhang;
			this._tenKhachHangTat = tenkhachhangtat;
			this._diaChi = diachi;
			this._maSoThue = masothue;
			this._dienThoai = dienthoai;
			this._mucChiTeu = mucchiteu;
			this._hidden = hidden;
		}
        /// <summary>
        /// Đếm tổng số bảng ghi.
        /// </summary>
        /// <returns></returns>
        public static int countRows(bool Hidden)
        {
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_KhachHang_Count", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
            try
            {
                con.Open();
                int intResult= (int)sqlComm.ExecuteScalar();
                return intResult;
            }
            catch (SqlException ex)
            {
                new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
                throw ex;
            }
            finally
            {
                if (sqlComm != null) sqlComm.Dispose();
                if (con != null) con.Close();
            }
        }
        /// <summary>
        /// Xác định Khóa chính có tồn tại dữ liệu.
        /// </summary>
        /// <returns>Truy: Có; False: Không</returns>
        public bool Exits_Key()
        {
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_KhachHang_Count_Key", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            try
            {
                sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = ID_KhachHang;
                con.Open();
                if (((int)sqlComm.ExecuteScalar()) == 1) return true;
                else return false;
            }
            catch (SqlException ex)
            {
                new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
                throw ex;
                return false;
            }
            finally
            {
                if (sqlComm != null) sqlComm.Dispose();
                if (con != null) con.Close();
            }
        }
        /// <summary>
        /// Xác định Khóa chính có tồn tại dữ liệu.
        /// </summary>
        /// <returns>Truy: Có; False: Không</returns>
        public static bool Exits_Key(int key_ID_KhachHang)
        {
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_KhachHang_Count_Key", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            try
            {
                sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = key_ID_KhachHang;
                con.Open();
                if (((int)sqlComm.ExecuteScalar()) == 1) return true;
                else return false;
            }
            catch (SqlException ex)
            {
                new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
                throw ex;
                return false;
            }
            finally
            {
                if (sqlComm != null) sqlComm.Dispose();
                if (con != null) con.Close();
            }
        }
        /// <summary>
        /// Get max ID auto.
        /// </summary>
        /// <returns></returns>
        public static int getMaxIdAuto()
        {
            SqlConnection con = new AccessDB().get_Conn();
        	string sSQL = "SELECT MAX(ID_KhachHang) FROM tbKhachHang";

            SqlCommand sqlComm = new SqlCommand(sSQL, con);
            sqlComm.CommandType = CommandType.Text;
            try
            {
                con.Open();
                return (int)sqlComm.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
                throw ex;
            }
            finally
            {
                if (sqlComm != null) sqlComm.Dispose();
                if (con != null) con.Close();
            }
        }
		/// <summary>
		/// Get DataTable NULL với table class.
		/// </summary>
		/// <returns></returns>
		public static DataTable getDataTable_NULL()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand("Select Top 0 * From tbKhachHang", con);
			sqlComm.CommandType = CommandType.Text;
			try
			{
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataTable dt = new DataTable();
		        con.Open();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <returns></returns>
		public static DataTable getDataTable_My_Key()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_KhachHang_SelectAll_Key, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataTable dt = new DataTable();
		        con.Open();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Get DataTable với Where time.
		/// </summary>
		/// <param name="strWhere">Where get ucChonNgay t</param>
		/// <returns></returns>
		public static DataTable getDataTable_Time(string strWhere)
		{
			try
			{
				string sSQL = "SELECT * FROM tbKhachHang" + strWhere;
				return getDataTable_SQL_pro(sSQL);
			}
			catch (SqlException ex)
			{
				return null;
			}
		}

		/// <summary>
		/// Get DataTable với Where.
		/// </summary>
		/// <returns></returns>
		public static DataTable getDataTable_Where(string strWhere,string FieldSort)
		{
			try
			{
			    if (FieldSort.Trim().Length == 0) FieldSort = "";
			    else FieldSort = " Order By " + FieldSort;
			    //
			    if (strWhere.Trim().Length == 0) strWhere = "";
			    else strWhere = " WHERE " + strWhere;
				string sSQL = "SELECT * FROM tbKhachHang" + strWhere + FieldSort ;
				return getDataTable_SQL_pro(sSQL);
			}
			catch (SqlException ex)
			{
				return null;
			}
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static DataTable getDataTable_SQL_pro(string sSQL)
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand("st_processSQL",con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			DataTable dt = new DataTable();
			try
			{
				sqlComm.Parameters.Add("@sSQL", SqlDbType.NVarChar).Value = sSQL;
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
		        con.Open();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, sSQL + " - " + ex.Message);
		        throw ex;
			}
            finally
            {
                if (dt != null) dt.Dispose();
                if (sqlComm != null) sqlComm.Dispose();
                if (con != null) con.Close();
            }
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static DataTable getDataTable_My()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_KhachHang_SelectAll, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
		    DataTable dt = new DataTable();
			try
			{
		        con.Open();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (dt != null) dt.Dispose();
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static DataSet getDataSet_My()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_KhachHang_SelectAll, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
		    DataSet ds = new DataSet();
			try
			{
		        con.Open();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (ds != null) ds.Dispose();
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		public static DataTable getDataTable_Hidden(bool Hidden)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    SqlCommand sqlComm = new SqlCommand("st_KhachHang_SelectAll_Hidden", con);
		    sqlComm.CommandType = CommandType.StoredProcedure;
		    sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;

		    SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
		    DataTable dt = new DataTable();
			try
			{
		        con.Open();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
				return null;
			}
		    finally
		    {
		        if (sqlDTA != null) sqlDTA.Dispose();
		        if (dt != null) dt.Dispose();
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		public static DataSet getDataSet_Hidden(bool Hidden)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    SqlCommand sqlComm = new SqlCommand("st_KhachHang_SelectAll_Hidden", con);
		    sqlComm.CommandType = CommandType.StoredProcedure;
		    sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;

		    SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
		    DataSet ds = new DataSet();
			try
			{
		        con.Open();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (sqlDTA != null) sqlDTA.Dispose();
		        if (ds != null) ds.Dispose();
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static DataTable getDataTable_My(int key_ID_KhachHang)
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_KhachHang_SelectByID, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
		        con.Open();
				//
				sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = key_ID_KhachHang;
				//
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataTable dt = new DataTable();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static DataSet getDataSet_My(int key_ID_KhachHang)
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_KhachHang_SelectByID, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
		        con.Open();
				//
				sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = key_ID_KhachHang;
				//
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataSet ds = new DataSet();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

	    public static int count_With_ID_LoaiHinhDoanhNghiep_ID_HeThongNuoc(string ID_LoaiHinhDoanhNghiep,string ID_HeThongNuoc)
	    {
	        SqlConnection con = new AccessDB().get_Conn();
	        SqlCommand sqlComm = new SqlCommand("st_KhachHang_CountBy_ID_LoaiHinhDoanhNghiep_ID_HeThongNuoc", con);
	        sqlComm.CommandType = CommandType.StoredProcedure;
	        //
	        sqlComm.Parameters.Add("@ID_LoaiHinhDoanhNghiep", SqlDbType.NVarChar).Value = ID_LoaiHinhDoanhNghiep;
	        sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = ID_HeThongNuoc;
	        try
	        {
	            con.Open();
	            return (int)sqlComm.ExecuteScalar();
	        }
	        catch (SqlException ex)
	        {
	            new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
	            throw ex;
	        }
	        finally
	        {
	            if (sqlComm != null) sqlComm.Dispose();
	            if (con != null) con.Close();
	        }
	    }
		public static DataTable getDataTable_With_ID_LoaiHinhDoanhNghiep(string ID_LoaiHinhDoanhNghiep,bool Hidden)
		{
		    return getDataSet_With_ID_LoaiHinhDoanhNghiep(ID_LoaiHinhDoanhNghiep,Hidden).Tables[0];
		}

		public static DataSet getDataSet_With_ID_LoaiHinhDoanhNghiep(string ID_LoaiHinhDoanhNghiep,bool Hidden)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    SqlCommand sqlComm = new SqlCommand("st_KhachHang_SelectByID_LoaiHinhDoanhNghiep", con);
		    sqlComm.CommandType = CommandType.StoredProcedure;
		    //
		    sqlComm.Parameters.Add("@ID_LoaiHinhDoanhNghiep", SqlDbType.NVarChar).Value = ID_LoaiHinhDoanhNghiep;
		    sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
		    //
			try
			{
		        con.Open();
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataSet ds = new DataSet();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		public static DataTable getDataTable_With_ID_LoaiHinhDoanhNghiep(string ID_LoaiHinhDoanhNghiep)
		{
		    return getDataSet_With_ID_LoaiHinhDoanhNghiep(ID_LoaiHinhDoanhNghiep).Tables[0];
		}

		public static DataSet getDataSet_With_ID_LoaiHinhDoanhNghiep(string ID_LoaiHinhDoanhNghiep)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    SqlCommand sqlComm = new SqlCommand("st_KhachHang_SelectByID_LoaiHinhDoanhNghiep_All",con);
		    sqlComm.CommandType = CommandType.StoredProcedure;
		    //
		    sqlComm.Parameters.Add("@ID_LoaiHinhDoanhNghiep", SqlDbType.NVarChar).Value = ID_LoaiHinhDoanhNghiep;
		    //
			try
			{
		        con.Open();
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataSet ds = new DataSet();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				return null;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

        /// <summary>
        /// Count record by LoaiTin.
        /// </summary>
        /// <param name="LoaiTin"></param>
        /// <param name="Hidden"></param>
        /// <returns></returns>
        public static int countByID_LoaiHinhDoanhNghiep(string ID_LoaiHinhDoanhNghiep,bool Hidden)
        {
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_KhachHang_CountByID_LoaiHinhDoanhNghiep", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            //
            sqlComm.Parameters.Add("@ID_LoaiHinhDoanhNghiep", SqlDbType.NVarChar).Value = ID_LoaiHinhDoanhNghiep;
            sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
            try
            {
                con.Open();
                return (int)sqlComm.ExecuteScalar();
            }
            catch (SqlException ex)
            {
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
            }
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
        }
		public static DataTable getDataTable_With_ID_HeThongNuoc(string ID_HeThongNuoc,bool Hidden)
		{
		    return getDataSet_With_ID_HeThongNuoc(ID_HeThongNuoc,Hidden).Tables[0];
		}

		public static DataSet getDataSet_With_ID_HeThongNuoc(string ID_HeThongNuoc,bool Hidden)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    SqlCommand sqlComm = new SqlCommand("st_KhachHang_SelectByID_HeThongNuoc", con);
		    sqlComm.CommandType = CommandType.StoredProcedure;
		    //
		    sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = ID_HeThongNuoc;
		    sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
		    //
			try
			{
		        con.Open();
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataSet ds = new DataSet();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		public static DataTable getDataTable_With_ID_HeThongNuoc(string ID_HeThongNuoc)
		{
		    return getDataSet_With_ID_HeThongNuoc(ID_HeThongNuoc).Tables[0];
		}

		public static DataSet getDataSet_With_ID_HeThongNuoc(string ID_HeThongNuoc)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    SqlCommand sqlComm = new SqlCommand("st_KhachHang_SelectByID_HeThongNuoc_All",con);
		    sqlComm.CommandType = CommandType.StoredProcedure;
		    //
		    sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = ID_HeThongNuoc;
		    //
			try
			{
		        con.Open();
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataSet ds = new DataSet();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				return null;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

        /// <summary>
        /// Count record by LoaiTin.
        /// </summary>
        /// <param name="LoaiTin"></param>
        /// <param name="Hidden"></param>
        /// <returns></returns>
        public static int countByID_HeThongNuoc(string ID_HeThongNuoc,bool Hidden)
        {
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_KhachHang_CountByID_HeThongNuoc", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            //
            sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = ID_HeThongNuoc;
            sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
            try
            {
                con.Open();
                return (int)sqlComm.ExecuteScalar();
            }
            catch (SqlException ex)
            {
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
            }
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
        }
		/// <summary>
		/// Thực hiện SQL dữ liệu.
		/// </summary>
		/// <returns></returns>
		public static bool doSQL(string sSQL)
		{
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_processSQL", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            sqlComm.Parameters.Add("@sSQL", SqlDbType.NVarChar).Value = sSQL;
			try
			{
                con.Open();
				sqlComm.ExecuteNonQuery();
				return true;
			}
			catch (SqlException  ex)
			{
		        throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện Insert dữ liệu.
		/// </summary>
		/// <returns></returns>
		public int doInsert()
		{
        SqlConnection con = new AccessDB().get_Conn();
		SqlCommand sqlComm = new SqlCommand(st_KhachHang_Insert,con);
        sqlComm.CommandType = CommandType.StoredProcedure;
        try
        {
				//
				//MSKH
				if(MSKH!=null)
				    sqlComm.Parameters.Add("@MSKH", SqlDbType.NVarChar).Value = MSKH;
				else
				    sqlComm.Parameters.Add("@MSKH", SqlDbType.NVarChar).Value = DBNull.Value;
				//ID_HeThongNuoc
				if(ID_HeThongNuoc!=null)
				    sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = ID_HeThongNuoc;
				else
				    sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = DBNull.Value;
				//ID_LoaiHinhDoanhNghiep
				if(ID_LoaiHinhDoanhNghiep!=null)
				    sqlComm.Parameters.Add("@ID_LoaiHinhDoanhNghiep", SqlDbType.NVarChar).Value = ID_LoaiHinhDoanhNghiep;
				else
				    sqlComm.Parameters.Add("@ID_LoaiHinhDoanhNghiep", SqlDbType.NVarChar).Value = DBNull.Value;
				//TenKhachHang
				if(TenKhachHang!=null)
				    sqlComm.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar).Value = TenKhachHang;
				else
				    sqlComm.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar).Value = DBNull.Value;
				//TenKhachHangTat
				if(TenKhachHangTat!=null)
				    sqlComm.Parameters.Add("@TenKhachHangTat", SqlDbType.NVarChar).Value = TenKhachHangTat;
				else
				    sqlComm.Parameters.Add("@TenKhachHangTat", SqlDbType.NVarChar).Value = DBNull.Value;
				//DiaChi
				if(DiaChi!=null)
				    sqlComm.Parameters.Add("@DiaChi", SqlDbType.NVarChar).Value = DiaChi;
				else
				    sqlComm.Parameters.Add("@DiaChi", SqlDbType.NVarChar).Value = DBNull.Value;
				//MaSoThue
				if(MaSoThue!=null)
				    sqlComm.Parameters.Add("@MaSoThue", SqlDbType.NVarChar).Value = MaSoThue;
				else
				    sqlComm.Parameters.Add("@MaSoThue", SqlDbType.NVarChar).Value = DBNull.Value;
				//DienThoai
				if(DienThoai!=null)
				    sqlComm.Parameters.Add("@DienThoai", SqlDbType.NVarChar).Value = DienThoai;
				else
				    sqlComm.Parameters.Add("@DienThoai", SqlDbType.NVarChar).Value = DBNull.Value;
			    sqlComm.Parameters.Add("@MucChiTeu", SqlDbType.Int).Value = MucChiTeu;
			    sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
                con.Open();
				sqlComm.ExecuteNonQuery();
                ID_KhachHang = getMaxIdAuto();
				return 1;
			}
			catch (SqlException  ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện update dữ liệu.
		/// </summary>
		/// <returns></returns>
		public int doUpdate()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_KhachHang_Update,con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
		        con.Open();
				//ID_KhachHang_find
				sqlComm.Parameters.Add("@ID_KhachHang_find", SqlDbType.Int).Value = ID_KhachHang_find;
				//MSKH
				if(MSKH!=null)
				    sqlComm.Parameters.Add("@MSKH", SqlDbType.NVarChar).Value = MSKH;
				else
				    sqlComm.Parameters.Add("@MSKH", SqlDbType.NVarChar).Value = DBNull.Value;
				//ID_HeThongNuoc
				if(ID_HeThongNuoc!=null)
				    sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = ID_HeThongNuoc;
				else
				    sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = DBNull.Value;
				//ID_LoaiHinhDoanhNghiep
				if(ID_LoaiHinhDoanhNghiep!=null)
				    sqlComm.Parameters.Add("@ID_LoaiHinhDoanhNghiep", SqlDbType.NVarChar).Value = ID_LoaiHinhDoanhNghiep;
				else
				    sqlComm.Parameters.Add("@ID_LoaiHinhDoanhNghiep", SqlDbType.NVarChar).Value = DBNull.Value;
				//TenKhachHang
				if(TenKhachHang!=null)
				    sqlComm.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar).Value = TenKhachHang;
				else
				    sqlComm.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar).Value = DBNull.Value;
				//TenKhachHangTat
				if(TenKhachHangTat!=null)
				    sqlComm.Parameters.Add("@TenKhachHangTat", SqlDbType.NVarChar).Value = TenKhachHangTat;
				else
				    sqlComm.Parameters.Add("@TenKhachHangTat", SqlDbType.NVarChar).Value = DBNull.Value;
				//DiaChi
				if(DiaChi!=null)
				    sqlComm.Parameters.Add("@DiaChi", SqlDbType.NVarChar).Value = DiaChi;
				else
				    sqlComm.Parameters.Add("@DiaChi", SqlDbType.NVarChar).Value = DBNull.Value;
				//MaSoThue
				if(MaSoThue!=null)
				    sqlComm.Parameters.Add("@MaSoThue", SqlDbType.NVarChar).Value = MaSoThue;
				else
				    sqlComm.Parameters.Add("@MaSoThue", SqlDbType.NVarChar).Value = DBNull.Value;
				//DienThoai
				if(DienThoai!=null)
				    sqlComm.Parameters.Add("@DienThoai", SqlDbType.NVarChar).Value = DienThoai;
				else
				    sqlComm.Parameters.Add("@DienThoai", SqlDbType.NVarChar).Value = DBNull.Value;
			    sqlComm.Parameters.Add("@MucChiTeu", SqlDbType.Int).Value = MucChiTeu;
				int up= sqlComm.ExecuteNonQuery();
				ID_KhachHang=ID_KhachHang_find;
				return up;
			}
			catch (SqlException  ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện update dữ liệu.
		/// </summary>
		/// <returns></returns>
		public int doUpdate_ALL()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_KhachHang_Update_ALL,con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
		        con.Open();
				//ID_KhachHang_find
				sqlComm.Parameters.Add("@ID_KhachHang_find", SqlDbType.Int).Value = ID_KhachHang_find;
				//MSKH
				sqlComm.Parameters.Add("@MSKH", SqlDbType.NVarChar).Value = MSKH;
				//ID_HeThongNuoc
				sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = ID_HeThongNuoc;
				//ID_LoaiHinhDoanhNghiep
				sqlComm.Parameters.Add("@ID_LoaiHinhDoanhNghiep", SqlDbType.NVarChar).Value = ID_LoaiHinhDoanhNghiep;
				//TenKhachHang
				sqlComm.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar).Value = TenKhachHang;
				//TenKhachHangTat
				sqlComm.Parameters.Add("@TenKhachHangTat", SqlDbType.NVarChar).Value = TenKhachHangTat;
				//DiaChi
				sqlComm.Parameters.Add("@DiaChi", SqlDbType.NVarChar).Value = DiaChi;
				//MaSoThue
				sqlComm.Parameters.Add("@MaSoThue", SqlDbType.NVarChar).Value = MaSoThue;
				//DienThoai
				sqlComm.Parameters.Add("@DienThoai", SqlDbType.NVarChar).Value = DienThoai;
				//MucChiTeu
				sqlComm.Parameters.Add("@MucChiTeu", SqlDbType.Int).Value = MucChiTeu;
				//Hidden
				sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
				int up = sqlComm.ExecuteNonQuery();
				ID_KhachHang=ID_KhachHang_find;
				return up;
			}
			catch (SqlException  ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện update dữ liệu.
		/// </summary>
		/// <returns></returns>
		public static int doUpdate_KEY(string Key_ID_KhachHang_Old,string Key_ID_KhachHang_New)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    string sql = "Update tbKhachHang Set ID_KhachHang=@ID_KhachHang_New Where ID_KhachHang=@ID_KhachHang_old";
			SqlCommand sqlComm = new SqlCommand(sql,con);
			sqlComm.CommandType = CommandType.Text;
			try
			{
			    sqlComm.Parameters.Add("@ID_KhachHang_New", SqlDbType.Int).Value = Key_ID_KhachHang_New;
			    sqlComm.Parameters.Add("@ID_KhachHang_old", SqlDbType.Int).Value = Key_ID_KhachHang_Old;
		        con.Open();
		        sqlComm.ExecuteNonQuery();
		        return 1;
		    }
		    catch (SqlException  ex)
		    {
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
		    }
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện update dữ liệu.
		/// </summary>
		/// <returns></returns>
		public int doUpdateHidden()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_KhachHang_Update_Hidden, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
				//ID_KhachHang
				sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = ID_KhachHang_find;
				sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
		        con.Open();
				sqlComm.ExecuteNonQuery();
				return 1;
			}
			catch (SqlException  ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				return -1;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện xóa dữ liệu.
		/// </summary>
		/// <returns></returns>
		public int doDelete()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_KhachHang_Delete, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
				sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = ID_KhachHang_find ;
				//
		        con.Open();
		    	sqlComm.ExecuteNonQuery() ;
				return 1;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Conver sang Object.
		/// </summary>
		/// <returns></returns>
		public static Cls_KhachHang converDataRow_To_Object(DataRow dr)
		{
			Cls_KhachHang _Object = new Cls_KhachHang();
		    if( dr[fn_ID_KhachHang]!= DBNull.Value )
		        _Object.ID_KhachHang =  (int)dr[fn_ID_KhachHang];
		    if( dr[fn_MSKH]!= DBNull.Value )
		        _Object.MSKH =  (string)dr[fn_MSKH];
		    if( dr[fn_ID_HeThongNuoc]!= DBNull.Value )
		        _Object.ID_HeThongNuoc =  (string)dr[fn_ID_HeThongNuoc];
		    if( dr[fn_ID_LoaiHinhDoanhNghiep]!= DBNull.Value )
		        _Object.ID_LoaiHinhDoanhNghiep =  (string)dr[fn_ID_LoaiHinhDoanhNghiep];
		    if( dr[fn_TenKhachHang]!= DBNull.Value )
		        _Object.TenKhachHang =  (string)dr[fn_TenKhachHang];
		    if( dr[fn_TenKhachHangTat]!= DBNull.Value )
		        _Object.TenKhachHangTat =  (string)dr[fn_TenKhachHangTat];
		    if( dr[fn_DiaChi]!= DBNull.Value )
		        _Object.DiaChi =  (string)dr[fn_DiaChi];
		    if( dr[fn_MaSoThue]!= DBNull.Value )
		        _Object.MaSoThue =  (string)dr[fn_MaSoThue];
		    if( dr[fn_DienThoai]!= DBNull.Value )
		        _Object.DienThoai =  (string)dr[fn_DienThoai];
		    if( dr[fn_MucChiTeu]!= DBNull.Value )
		        _Object.MucChiTeu =  (int)dr[fn_MucChiTeu];
		    if( dr[fn_Hidden]!= DBNull.Value )
		        _Object.Hidden =  (bool)dr[fn_Hidden];
		    //============
		    return _Object;
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static Cls_KhachHang getOject_Key(int key_ID_KhachHang)
		{
		    DataTable dt = getDataTable_My(key_ID_KhachHang);
		    //==========================
		    if (dt.Rows.Count > 0)
		        return converDataRow_To_Object(dt.Rows[0]);
		    else
		        return null;
		}

		/// <summary>
		/// Get Array object class.
		/// </summary>
		/// <returns></returns>
		public static Cls_KhachHang[] getArrayObject(DataTable dt)
		{
		    if (dt.Rows.Count == 0) return null;
		    //
		    Cls_KhachHang[] arr = new Cls_KhachHang[dt.Rows.Count];
		    int i = 0;
		    foreach (DataRow dr in dt.Rows)
		    {
		    
		        arr[i] = converDataRow_To_Object( dr  );
		        i++;
		    }
		    return arr;
		}

		/// <summary>
		/// Get Array object class.
		/// </summary>
		/// <returns></returns>
		public static Cls_KhachHang[] getArrayObject()
		{
		    return getArrayObject( getDataTable_My() );
		}

	}

}
